﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using VSDBM.Code.dto;
using System.Data;
using MySql.Data.MySqlClient;

namespace VSDBM.Code.dao
{
    public class SequenceDAO
    {


        public static String add(Sequence sequence)
        {
            MySqlConnection connection = Connection.getNewConnection();
            try
            {

                
                
                    MySqlCommand query = new MySqlCommand("INSERT INTO sequences (definition,idacession,idorganism,idpubmed,iduser_register,locus,sequence,size,status) VALUES (\"" + sequence.definition + "\",\"" + sequence.idacession + "\"," + sequence.idorganism + "," + sequence.idpubmed + "," + sequence.iduser_register + ",\"" + sequence.locus + "\",\"" + sequence.sequence + "\"," + sequence.size + "," + sequence.status + ")", connection);

                    query.ExecuteNonQuery();
                    connection.Close();
                    return "Added!";
                    
                
                    
                    
                
            }
           
            catch (Exception e)
            {
                
                connection.Close();
                throw;
            }
        }

        public static Boolean delete(Sequence sequence)
        {
            MySqlConnection connection = Connection.getConnection();
            try
            {
                MySqlCommand query3 = new MySqlCommand("DELETE FROM group_sequences WHERE idsequence = " + sequence.id + "", connection);
                query3.ExecuteNonQuery();
                MySqlCommand query2 = new MySqlCommand("DELETE FROM sequence_map WHERE idsequence = " + sequence.id + "", connection);
                query2.ExecuteNonQuery();
                MySqlCommand query1 = new MySqlCommand("DELETE FROM sequence_subtype WHERE idsequence = " + sequence.id + "", connection);
                query1.ExecuteNonQuery();
                MySqlCommand query = new MySqlCommand("DELETE FROM sequences WHERE id = " + sequence.id + "", connection);
                query.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public static DataSet select(String filters, String orderby)
        {
            MySqlConnection connection = Connection.getNewConnection();
            DataSet dSet = new DataSet();
            try
            {

                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand("SELECT * FROM sequences " + filters + " " + orderby, connection);

                adapter.Fill(dSet);
                connection.Close();
                return dSet;
            }
            catch (Exception e)
            {
                connection.Close();
                return dSet;
               
            }
        }

        public static DataSet selectbuscador(String filters)
        {
            MySqlConnection connection = Connection.getConnection();
            DataSet dSet = new DataSet();
            try
            {

                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand("SELECT s.*, sm.*, org.*, st.*, rf.*, rf.description as featuremap FROM sequences s JOIN sequence_subtype ss ON (s.id = ss.idsequence) 	JOIN sequence_map sm ON (s.id = sm.idsequence) 	JOIN organisms org ON (s.idorganism = org.id) JOIN subtypes st ON (ss.idsubtype = st.id) JOIN reference_features rf ON (sm.idfeature = rf.id) " + filters, connection);
                adapter.Fill(dSet);
                return dSet;
            }
            catch (Exception e)
            {
                return dSet;
            }
        }

        public static Sequence Fill(int id, String definition, String idacession, int idorganism, int idpubmed, int iduser_register, String locus, String sequence, int size, int status)
        {

            Sequence sequencen = new Sequence();
            sequencen.id = id;
            sequencen.definition = definition;
            sequencen.idacession = idacession;
            sequencen.idorganism = idorganism;
            sequencen.idpubmed = idpubmed;
            sequencen.iduser_register = iduser_register;
            sequencen.locus = locus;
            sequencen.sequence = sequence;
            sequencen.size = size;
            sequencen.status = status;



            return sequencen;
        }

        public static List<Sequence> DStoList(DataSet ds)
        {

            List<Sequence> list = new List<Sequence>();
            try
            {
                for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    DataTable registro = ds.Tables[0];
                    list.Add(Fill(int.Parse(registro.Rows[i]["id"].ToString()), registro.Rows[i]["definition"].ToString(), registro.Rows[i]["idacession"].ToString(), int.Parse(registro.Rows[i]["idorganism"].ToString()), int.Parse(registro.Rows[i]["idpubmed"].ToString()), int.Parse(registro.Rows[i]["iduser_register"].ToString()), registro.Rows[i]["locus"].ToString(), registro.Rows[i]["sequence"].ToString(), int.Parse(registro.Rows[i]["size"].ToString()), int.Parse(registro.Rows[i]["status"].ToString())));
                }

            }
            catch (Exception e) { }
            return list;
        }

        public static void setMapped(int id)
        {

            MySqlConnection connection = Connection.getConnection();
            DataSet dSet = new DataSet();
            try
            {

                MySqlCommand query = new MySqlCommand("UPDATE sequences SET status=1 WHERE id="+id, connection);
                query.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void setUnMapped(int id)
        {

            MySqlConnection connection = Connection.getConnection();
            DataSet dSet = new DataSet();
            try
            {

                MySqlCommand query = new MySqlCommand("UPDATE sequences SET status=0 WHERE id=" + id, connection);
                query.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static DataSet getReferenceTotalSize(int idfeature)
        {

            MySqlConnection connection = Connection.getConnection();
            DataSet dSet = new DataSet();
            try
            {

                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand("SELECT s.size as size FROM  reference_features rf 	JOIN references_sequences rs ON (rf.idseqref = rs.id) 	JOIN sequences s ON (rs.idsequence = s.id) WHERE rf.id = " + idfeature, connection);
                adapter.Fill(dSet);
                return dSet;
            }
            catch (Exception e)
            {
                return dSet;
            }
        }

    }
}